<?php

abstract class DBOSQLDatabase extends DBODatabase implements ISQLDatabase, ArrayAccess, Countable
{
	public function dropTable($tableName, $raw = false)
	{
		$t = $this->getTable($tableName, DBCREATE_NONE, $raw);
		if($t)
		{
			$t->drop();
		}
	}

	protected function qualifyTableName($name)
	{
		return '"' . $name . '"';
	}

	public function registerTable($ident, $name)
	{
		$name = $this->qualifyTableName($name);
		$this->tables[$ident] = $name;
		return $this->connection->registerTable($ident, $name, $this);
	}

	public function getTable($name, $createMode = DBCREATE_NONE, $raw = false, $useCache = false)
	{
		if($raw)
		{
			return $this->connection->getTable($this, $name, $createMode);
		}
		if($useCache && isset($this->tableObjects[$name]))
		{
			return $this->tableObjects[$name];
		}
		if(!isset($this->tables[$name]))
		{
			throw new DatabaseException('Table ' . $name . ' has not been registered', -1);
			return null;
		}
		$t = $this->connection->getTable($this, $this->tables[$name], $createMode);
		if($t)
		{
			$this->tableObjects[$name] = $t;
		}
		return $t;
	}

	public function getTableName($ident)
	{
		if(isset($this->tables[$ident]))
		{
			return $this->tables[$ident];
		}
		return $this->connection->getTableName($ident);
	}


	/* Safely format a value for inclusion in a query. Note that NULL values are
	 * treated as empty strings.
	 */
 	public function quote($s)
	{
		return $this->masterConnection->quote($s);
	}

	/* Prepare and execute a SELECT-type query; returns an object compatible with IRecordSet */
	protected function execute($sql, $params, $offset, $limit, $fetch = true)
	{
		$this->lastAffectedRows = null;
		$st = null;
		$st =& $this->prepare($sql, $offset, $limit);
		if(!($st->_execute($params, $fetch, 3)))
		{
			$st = false;
		}
		else
		{
			$this->lastAffectedRows = $st->rowCount();
		}
		return $st;
	}

	public function affectedRows()
	{
		return $this->lastAffectedRows;
	}

	public function insert($table, $values, $raw = false)
	{
		$q = 'INSERT INTO ' . ($raw ? $table : '[' . $table. ']');
		$fl = array();
		$vl = array();
		$args = array();
		foreach($values as $k => $val)
		{
			if(substr($k, 0, 1) == '@')
			{
				$k = substr($k, 1);
				$v = $val;
			}
			else if($val === null)
			{
				$v = 'NULL';
			}
			else
			{
				$v = '?';
				$args[] = $val;
			}
			$fl[] = '"' . $k . '"';
			$vl[] = $v;
		}
		$q .= '(' . implode(', ', $fl) . ') VALUES (' . implode(', ', $vl) . ')';
		return $this->execute($q, $args, 0, 0, true);
	}

	/* Perform a query; returns an object compatible with IRecordSet */
	public function query($sql)
	{
		$params = func_get_args();
		array_shift($params);
		return $this->execute($sql, $params, 0, 0, true);
	}

	public function queryArgs($sql, $params)
	{
		$rs =& $this->execute($sql, $params, 0, 0, true);
		return $rs;
	}

	public function queryPaginated($sql, $offset = 0, $limit = 25)
	{
		$params = func_get_args();
		array_shift($params);
		array_shift($params);
		array_shift($params);
		$rs =& $this->execute($sql, $params, $offset, $limit, true);
		return $rs;
	}

	public function queryPaginatedArgs($sql, $params, $offset = 0, $limit = 25)
	{
		$rs =& $this->execute($sql, $params, $offset, $limit, true);
		return $rs;
	}

	public function getAllPaginated($sql, $offset = 0, $limit = 0)
	{
		$params = func_get_args();
		array_shift($params);
		array_shift($params);
		array_shift($params);
		$rs = $this->execute($sql, $params, $offset, $limit, false);
		return $rs->getAll();
	}

	public function getAll($sql)
	{
		$params = func_get_args();
		array_shift($params);
		$rs = $this->execute($sql, $params, 0, 0, false);
		return $rs->getAll();
	}


	public function getRow($sql)
	{
		$params = func_get_args();
		array_shift($params);
		$rs = $this->execute($sql, $params, 0, 1, false);
		return $rs->getRow();
	}

	public function getRowArgs($sql, $params)
	{
		$rs = $this->execute($sql, $params, 0, 1, false);
		return $rs->getRow();
	}


	public function getOne($sql)
	{
		$params = func_get_args();
		array_shift($params);
		$rs = $this->execute($sql, $params, 0, 1, false);
		return $rs->getOne();
	}

	/* As getAll, except the first column in the result-set is used as keys
	 * for the results.
	 */
	public function getAssoc($sql)
	{
		$params = func_get_args();
		array_shift($params);
		$rs = $this->execute($sql, $params, 0, 0, false);
		return $rs->getAssoc();
	}

	/* Return a single-dimensional array containing the values from the first
	 * column in the resultset.
	 */
	public function getCol($sql)
	{
		$params = func_get_args();
		array_shift($params);
		$rs = $this->execute($sql, $params, 0, 0, false);
		return $rs->getCol();
	}

	public function now()
	{
		return $this->quote(strftime('%Y-%m-%d %H:%M:%S'));
	}

	public function append($newval)
	{
		raise('Database objects cannot be appended.');
	}
	
	public function count()
	{
		return count($this->tables);
	}
	
	public function offsetExists($index)
	{
		return isset($this->tables[$index]);
	}

	public function offsetGet($index)
	{
		return $this->getTable($index, DBCREATE_NONE, false, true);
	}
	
	public function offsetSet($index, $value)
	{
		$this->registerTable($index, $value);
	}
	
	public function offsetUnset($index)
	{
		raise('Database tables cannot be unset');
	}
	
	public function updateModuleVersion($ident, $version, $realmUUID)
	{
		$modules = $this->qualifyTableName('_modules');
		$modstatus = $this->qualifyTableName('_modules_status');
		$insert = false;
		try
		{
			if(null == $this->getOne('SELECT "module_ident" FROM ' . $modules . ' WHERE "module_ident" = ?', $ident))
			{
				$insert = true;
			}
		}
		catch(DatabaseException $e)
		{
			$insert = true;
			$t = $this->getTable($modules, DBCREATE_NOTEXISTS, true);
			$t->addCol('module_ident', DBTYPE_VARCHAR, 64, DBFLAGS_NOTNULL|DBFLAGS_PRIMARY, 'Module identifier');
			$t->addCol('module_version', DBTYPE_INT, null, DBFLAGS_NOTNULL, 'Current module schema version (-1 = placeholder)');
			$t->addCol('module_updated', DBTYPE_DATETIME, null, DBFLAGS_NOTNULL, 'Timestamp of the module being updated');
			$t->addCol('module_global', DBTYPE_BOOLEAN, null, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'If "Y" this module is loaded application-wide, otherwise per-realm', 'N');
			$t->comment = 'Available application modules';
			$t->commit();
		}
		if($insert)
		{
			$this->insert($modules, array(
				'module_ident' => $ident,
				'@module_updated' => $this->now(),
				'module_version' => $version,
				'module_global' => ($realmUUID == null ? 'Y' : 'N'),
				), true);
		}
		else
		{
			$this->query('UPDATE ' . $modules . ' SET "module_updated" = ' . $this->now() . ', "module_version" = ? WHERE "module_ident" = ?', $version, $ident);
		}
		$insert = false;
		try
		{
			if(null == $this->getOne('SELECT "module_ident" FROM ' . $modstatus . ' WHERE "module_ident" = ? AND "realm_uuid" = ?', $ident, $realmUUID))
			{
				$insert = true;
			
			}
		}
		catch(DatabaseException $e)
		{
			$insert = true;
			$t = $this->getTable($modstatus, DBCREATE_NOTEXISTS, true);
			$xident = $t->addCol('module_ident', DBTYPE_VARCHAR, 64, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'Module identifier');
			$t->addCol('module_enabled', DBTYPE_BOOLEAN, null, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'Indicates whether this module is enabled or not', 'N');
			$xrealm = $t->addCol('realm_uuid', DBTYPE_UUID, null, DBFLAGS_NULL|DBFLAGS_INDEX, 'Realm UUID');
			$t->addCol('module_dataversion', DBTYPE_INT, null, DBFLAGS_NULL, 'Module data version');
			$t->addCol('module_updated', DBTYPE_DATETIME, null, DBFLAGS_NOTNULL, 'Timestamp of this module’s data being updated');
			$t->addIndex(null, DBINDEX_PRIMARY, $xident, $xrealm);
			$t->comment = 'Per-realm module data version information';
			$t->commit();		
		}
		if($insert)
		{
			if(!strlen($realmUUID)) $realmUUID = null;
			$this->insert($modstatus, array(
				'module_ident' => $ident,
				'module_enabled' => 'Y',
				'realm_uuid' => $realmUUID,
				'@module_updated' => $this->now(),
				), true);
		}
	}
	
	public function updateModuleDataVersion($ident, $version, $realmUUID)
	{
		$modstatus = $this->qualifyTableName('_modules_status');
		$insert = false;
		try
		{
			if(null == $this->getOne('SELECT "module_ident" FROM ' . $modstatus . ' WHERE "module_ident" = ? AND "realm_uuid" = ?', $ident, $realmUUID))
			{
				$insert = true;
			
			}
		}
		catch(DatabaseException $e)
		{
			$insert = true;
			$t = $this->getTable($modstatus, DBCREATE_NOTEXISTS, true);
			$xident = $t->addCol('module_ident', DBTYPE_VARCHAR, 64, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'Module identifier');
			$t->addCol('module_enabled', DBTYPE_BOOLEAN, null, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'Indicates whether this module is enabled or not', 'N');
			$xrealm = $t->addCol('realm_uuid', DBTYPE_UUID, null, DBFLAGS_NOTNULL|DBFLAGS_INDEX, 'Realm UUID');
			$t->addCol('module_dataversion', DBTYPE_INT, null, DBFLAGS_NULL, 'Module data version');
			$t->addCol('module_updated', DBTYPE_DATETIME, null, DBFLAGS_NOTNULL, 'Timestamp of this module’s data being updated');
			$t->addIndex(null, DBINDEX_PRIMARY, $xident, $xrealm);
			$t->comment = 'Per-realm module data version information';
			$t->commit();		
		}
		if($insert)
		{
			$this->insert($modstatus, array(
				'module_ident' => $ident,
				'module_enabled' => 'Y',
				'realm_uuid' => $realmUUID,
				'@module_updated' => $this->now(),
				'module_dataversion' => $version,
				), true);
		}
		else
		{
			$this->db->query('UPDATE ' . $modstatus . ' SET "module_dataversion" = ? WHERE "module_ident" = ? AND "realm_uuid" = ?',
				$version, $ident, $realmUUID);
		}
	
	}
}
